﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Blizzard.Scripts
{
  public  class SqlServerSyntax : IDDLSyntax
    {
        public string CreateScript(List<Table> tables)
        {
            string mainScript = "";
            foreach (Table table in tables)
            {
                string tableName = table.Name;
                tableName = tableName.Replace("[", "");
                tableName = tableName.Replace("]", "");

                string identityField = table.IdentityFieldName;
                identityField = identityField.Replace("[", "");
                identityField = identityField.Replace("]", "");


                string script = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + tableName + "]') AND type in (N'U'))\r\n";
                script = script + "BEGIN\r\n";
                script = script + "CREATE TABLE [dbo].[" + tableName + "](\r\n";
                script = script + "[" + identityField + "] [int] IDENTITY(1,1) NOT NULL,\r\n";

                foreach (Table.Field field in table.Fields)
                {

                    string fieldName = field.Name;
                    fieldName = fieldName.Replace("[", "");
                    fieldName = fieldName.Replace("]", "");

                    if (field.DotNetFieldType == typeof(string))
                    {
                        script = script + "[" + fieldName + "] [" + typeConverter(field.DotNetFieldType) + "](50) NOT NULL,\r\n";
                    }
                    else 
                    {
                        script = script + "[" + fieldName + "] [" + typeConverter(field.DotNetFieldType) + "] NOT NULL,\r\n";
                    }

                }
                // to remove the last field comma
                script = script.TrimEnd(',' , '\r' , '\n');

                script = script + "\r\n";

                script = script + ") ON [PRIMARY]\r\n";
                script = script + "END\r\n";
                script = script + "GO\r\n\r\n";

                mainScript = mainScript + script;
            }

            return mainScript;
        }


        private string typeConverter(Type fieldType)
        {

            switch (fieldType.ToString())
            {

                case "System.String":
                    return "nvarchar";

                case "System.Int32":
                    return "int";

                case "System.DateTime":
                    return "datetime";

                default:
                    return "nvarchar";
            }
        
        }


    }
}

/*

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[person]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[person](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [InsertionDate] [datetime] NOT NULL,
    [BBid] [int] NOT NULL
) ON [PRIMARY]
END
GO

*/